﻿# -*- coding:utf-8 -*-
import sqlite3
from collections import OrderedDict
#-----------------------------------------------用户原创表-—---------------------------------------------#
def use_poem_opendb():
    conn = sqlite3.connect("poem_date.db")
    cur = conn.execute("""create table if not exists use_poem_info(openid varchar(56),title varchar(56),author varchar(56),poem varchar(256),imgurl varchar(126))""")
    return cur,conn

def use_poem_insertData(openid,title,author,poem,imgurl):
    hel = use_poem_opendb()
    hel[1].execute("insert into use_poem_info(openid,title,author,poem,imgurl) values ('%s','%s','%s','%s','%s')"%(openid,title,author,poem,imgurl))
    hel[1].commit()
    hel[1].close()

#根据查找用户整体信息
def use_poem_select():
    hel = use_poem_opendb()
    cur = hel[1].cursor()
    cur.execute("select * from use_poem_info")
    res = cur.fetchall()
    return res
    cur.close()

#根据openid查找用户整体信息
def use_poem_selectOpenid(openid):
    hel = use_poem_opendb()
    cur = hel[1].cursor()
    cur.execute("select * from use_poem_info where openid='%s'" %openid)
    res = cur.fetchall()
    return res
    cur.close()

#根据查找用户整体信息
def use_poem_selectall(strs):
    hel = use_poem_opendb()
    cur = hel[1].cursor()
    cur.execute("select * from use_poem_info where title like '%%%s%%' or author like '%%%s%%' or poem like '%%%s%%'"%(strs,strs,strs))
    res = cur.fetchall()
    return res
    cur.close()

def use_poem_delOpenid(openid):
    hel = use_poem_opendb()
    cur = hel[1].cursor()
    cur.execute("delete from use_poem_info where openid='%s'" %openid)
    print("已删除！")
    hel[1].commit()
    hel[1].close()

def use_poem_delpoem(poem):
    hel = use_poem_opendb()
    cur = hel[1].cursor()
    cur.execute("delete from use_poem_info where poem='%s'" %poem)
    print("已删除！")
    hel[1].commit()
    hel[1].close()


# ----------------------------------------------用户信息表------------------------------------------------#
def use_opendb():
        conn = sqlite3.connect("poem_date.db")
        cur = conn.execute(
                """create table if not exists use_info(openid varchar(56),nickname varchar(56),imgurl varchar(216),gender varchar(4),sign varchar(56))""")
        return cur, conn


def use_insertData(openid, nickname, imgurl, gender, sign):
        hel = use_opendb()
        hel[1].execute("insert into use_info(openid,nickname,imgurl,gender,sign) values ('%s','%s','%s','%s','%s')" % (
        openid, nickname, imgurl, gender, sign))
        hel[1].commit()
        hel[1].close()

#根据openid查找用户整体信息
def use_selectOpenid(openid):
    hel = use_opendb()
    cur = hel[1].cursor()
    cur.execute("select * from use_info where openid='%s'" %openid)
    res = cur.fetchall()
    return res
    cur.close()
 
#根据openid查找用户整体信息
def use_select_all():
    hel = use_opendb()
    cur = hel[1].cursor()
    cur.execute("select * from use_info")
    res = cur.fetchall()
    return res
    cur.close()
    

def use_delOpenid(openid):
    use_audio_delOpenid(openid)
    print("用户语音数据库已删除")
    use_poem_delOpenid(openid)
    print("用户原创数据库已删除")
    hel = use_opendb()
    cur = hel[1].cursor()
    cur.execute("delete from use_info where openid='%s'" %openid)
    print("用户信息已删除！")
    hel[1].commit()
    hel[1].close()

def use_alterOpenid(nickname,sign,openid):
    hel = use_opendb()
    cur = hel[1].cursor()
    cur.execute("update use_info set nickname='%s',sign='%s' where openid='%s'"%(nickname,sign,openid))
    print("资料已修改！")
    hel[1].commit()
    hel[1].close()

#------------------------------------------------两表连接查询----------------------------------------------------#

def use_audioAndpoem(nickname):
    conn = sqlite3.connect("poem_date.db")
    cur = conn.cursor()
    cur.execute("SELECT u.nickname,a.audioname,a.poemname,a.author,a.poem  FROM use_info AS u INNER JOIN use_audio_info AS a ON u.openid = a.openid WHERE u.nickname like '%%%s%%'"%nickname)
    res = cur.fetchall()
    return res
    cur.close()

# ---------------------------------------------用户语音-----------------------------------------------------#
def use_audio_opendb():
        conn = sqlite3.connect("poem_date.db")
        cur = conn.execute(
                """create table if not exists use_audio_info(openid varchar(56),audioname varchar(216),poemname varchar(50),author varchar(30),poem varchar(256),sm varchar(5))""")
        return cur, conn


def use_audio_insertData(openid, audioname, poemname, author, poem, sm):
        hel = use_audio_opendb()
        hel[1].execute(
                "insert into use_audio_info(openid,audioname,poemname,author,poem,sm) values ('%s','%s','%s','%s','%s','%s')" % (
                openid, audioname, poemname, author, poem, sm))
        hel[1].commit()
        hel[1].close()

#根据openid查找用户整体信息
def use_audio_selectOpenid(openid):
    hel = use_audio_opendb()
    cur = hel[1].cursor()
    cur.execute("select * from use_audio_info where openid='%s'" %openid)
    res = cur.fetchall()
    return res
    cur.close()

#查找用户整体信息
def use_audio_select():
    hel = use_audio_opendb()
    cur = hel[1].cursor()
    cur.execute("select * from use_audio_info")
    res = cur.fetchall()
    return res
    cur.close()

def use_audio_delOpenid(openid):
    hel = use_audio_opendb()
    cur = hel[1].cursor()
    cur.execute("delete from use_audio_info where openid='%s'" %openid)
    print("已删除！")
    hel[1].commit()
    hel[1].close()

def use_audio_delaudioname(audioname):
    hel = use_audio_opendb()
    cur = hel[1].cursor()
    cur.execute("delete from use_audio_info where audioname='%s'" %audioname)
    print("已删除！")
    hel[1].commit()
    hel[1].close()

#根据查找用户整体信息
def use_audio_selectall(strs):
    hel = use_audio_opendb()
    cur = hel[1].cursor()
    cur.execute("select * from use_audio_info where poemname like '%%%s%%' or author like '%%%s%%' or poem like '%%%s%%'"%(strs,strs,strs))
    res = cur.fetchall()
    return res
    cur.close()

#----------------------------------------ciauthor---------------------------------------------------#
def ci_author_opendb():
    conn = sqlite3.connect("poem_date.db")
    cur = conn.execute("""create table if not exists ci_author_info(id integer PRIMARY KEY autoincrement,name varchar(30),long_content text,content text)""")
    return cur,conn

#------------------------------------author--------------------------------------------------------#
def tang_author_opendb():
    conn = sqlite3.connect("poem_date.db")
    cur = conn.execute("""create table if not exists tang_author_info(id integer PRIMARY KEY autoincrement,name varchar(30),content text)""")
    return cur,conn

def song_author_opendb():
    conn = sqlite3.connect("poem_date.db")
    cur = conn.execute("""create table if not exists song_author_info(id integer PRIMARY KEY autoincrement,name varchar(30),content text)""")
    return cur,conn

#-------------------------------ci------------------------------------------------------------#

def ci_poem_opendb():
    conn = sqlite3.connect("poem_date.db")
    cur = conn.execute("""create table if not exists ci_info(id integer PRIMARY KEY autoincrement,poemName varchar(30),author varchar(30),content text)""")
    return cur,conn

#根据诗句模糊查找诗整体信息
def ci_poem_selectParagraphs(paragraphs):
        hel = ci_poem_opendb()
        cur = hel[1].cursor()
        cur.execute("select * from ci_info where content like '%%%s%%'" %paragraphs)
        res = cur.fetchall()
        return res
        cur.close()
        
#根据诗名查找诗整体信息
def ci_poem_selectName(poemName):
        hel = ci_poem_opendb()
        cur = hel[1].cursor()
        cur.execute("select * from ci_info where poemName='%s'" %poemName)
        res = cur.fetchall()
        return res

#根据诗句模糊查找诗整体信息
def ci_poem_selectParagraphss(strs):
        hel = ci_poem_opendb()
        cur = hel[1].cursor()
        cur.execute("select * from ci_info where poemName like '%%%s%%' or author like '%%%s%%' or content like '%%%s%%'" %(strs,strs,strs))
        res = cur.fetchall()
        return res
        cur.close()
        
#根据作者查找诗整体信息
def ci_poem_selectAuthor(author):
        hel = ci_poem_opendb()
        cur = hel[1].cursor()
        cur.execute("select * from ci_info where author='%s'" %author)
        res = cur.fetchall()
        return res
        cur.close()
#-------------------------------tang---------------------------------------------------------------#
def tang_poem_opendb():
    conn = sqlite3.connect("poem_date.db")
    cur = conn.execute("""create table if not exists tang_poem_info(id integer PRIMARY KEY autoincrement,poemName varchar(30),author varchar(30),strains varchar(256),paragraphs varchar(256))""")
    return cur,conn

#  往数据库中添加内容
def tang_poem_insertData(poemName,author,strains,paragraphs):
        hel = tang_poem_opendb()
        hel[1].execute("insert into tang_poem_info(poemName,author,strains,paragraphs) values ('%s','%s','%s','%s')"%(poemName,author,strains,paragraphs))
        hel[1].commit()
        hel[1].close()
        
#   删除数据库中的全部内容
def tang_poem_delall():
        hel = tang_poem_opendb()              # 返回游标conn
        hel[1].execute("delete from tang_poem_info")
        print("删库跑路Cxk我最帅")
        hel[1].commit()
        hel[1].close()
        
#根据诗名与作者查询诗
def tang_poem_show(poemName,author):
        hel = tang_poem_opendb()
        cur = hel[1].cursor()
        cur.execute("select paragraphs from tang_poem_info where poemName='%s' and author='%s'" %(poemName,author))
        res = cur.fetchall()
        return res
        cur.close()
        
#根据诗句模糊查找诗整体信息
def tang_poem_selectParagraphs(paragraphs):
        hel = tang_poem_opendb()
        cur = hel[1].cursor()
        cur.execute("select * from tang_poem_info where paragraphs like '%%%s%%'" %paragraphs)
        res = cur.fetchall()
        return res
        cur.close()

#根据诗句模糊查找诗整体信息
def tang_poem_selectParagraphss(strs):
        hel = tang_poem_opendb()
        cur = hel[1].cursor()
        cur.execute("select * from tang_poem_info where poemName like '%%%s%%' or author like '%%%s%%' or paragraphs like '%%%s%%'" %(strs,strs,strs))
        res = cur.fetchall()
        return res
        cur.close()
        
#根据诗名查找诗整体信息
def tang_poem_selectName(poemName):
        hel = tang_poem_opendb()
        cur = hel[1].cursor()
        cur.execute("select * from tang_poem_info where poemName='%s'" %poemName)
        res = cur.fetchall()
        return res
        cur.close()
        
#根据作者查找诗整体信息
def tang_poem_selectAuthor(author):
        hel = tang_poem_opendb()
        cur = hel[1].cursor()
        cur.execute("select * from tang_poem_info where author='%s'" %author)
        res = cur.fetchall()
        return res
        cur.close()
        
#------------------------------------song----------------------------------------------------------#


def song_poem_opendb():
    conn = sqlite3.connect("poem_date.db")
    cur = conn.execute("""create table if not exists song_poem_info(id integer PRIMARY KEY autoincrement,poemName varchar(30),author varchar(30),strains varchar(256),paragraphs varchar(256))""")
    return cur,conn

#  往数据库中添加内容
def song_poem_insertData(poemName,author,strains,paragraphs):
        hel = song_poem_opendb()
        hel[1].execute("insert into song_poem_info(poemName,author,strains,paragraphs) values ('%s','%s','%s','%s')"%(poemName,author,strains,paragraphs))
        hel[1].commit()
        hel[1].close()
        
#   删除数据库中的全部内容
def song_poem_delall():
        hel = song_poem_opendb()              # 返回游标conn
        hel[1].execute("delete from song_poem_info")
        print("删库跑路Cxk我最帅")
        hel[1].commit()
        hel[1].close()
        
#根据诗名与作者查询诗
def song_poem_show(poemName,author):
        hel = song_poem_opendb()
        cur = hel[1].cursor()
        cur.execute("select paragraphs from song_poem_info where poemName='%s' and author='%s'" %(poemName,author))
        res = cur.fetchall()
        return res
        cur.close()
        
#根据诗句模糊查找诗整体信息
def song_poem_selectParagraphs(paragraphs):
        hel = song_poem_opendb()
        cur = hel[1].cursor()
        cur.execute("select * from song_poem_info where paragraphs like '%%%s%%'" %paragraphs)
        res = cur.fetchall()
        return res
        cur.close()

#根据诗句模糊查找诗整体信息
def song_poem_selectParagraphss(strs):
        hel = song_poem_opendb()
        cur = hel[1].cursor()
        cur.execute("select * from song_poem_info where poemName like '%%%s%%' or author like '%%%s%%' or paragraphs like '%%%s%%'" %(strs,strs,strs))
        res = cur.fetchall()
        return res
        cur.close()
        
#根据诗名查找诗整体信息
def song_poem_selectName(poemName):
        hel = song_poem_opendb()
        cur = hel[1].cursor()
        cur.execute("select * from song_poem_info where poemName='%s'" %poemName)
        res = cur.fetchall()
        return res
        cur.close()
        
#根据作者查找诗整体信息
def song_poem_selectAuthor(author):
        hel = song_poem_opendb()
        cur = hel[1].cursor()
        cur.execute("select * from song_poem_info where author='%s'" %author)
        res = cur.fetchall()
        return res
        cur.close()


#-----------------------------------------------------------------------鸡汤/毒汤文-------------------------------------------------------
def jitang_opendb():
    conn = sqlite3.connect("jitang_date.db")
    cur = conn.execute(
        """create table if not exists jitang_info(id integer PRIMARY KEY autoincrement,jitang varchar(256))""")
    return cur, conn


#  往数据库中添加内容
def jitang_insertData(jitang):
    hel = jitang_opendb()
    hel[1].execute("insert into jitang_info(jitang) values ('%s')" % (jitang))
    hel[1].commit()
    hel[1].close()


# 根据诗名查找诗整体信息
def jitang_select():
    hel = jitang_opendb()
    cur = hel[1].cursor()
    cur.execute("SELECT * FROM jitang_info ORDER BY RANDOM() limit 1")
    res = cur.fetchall()
    return res
    cur.close()

#根据诗句模糊查找诗整体信息
def jitang_selectParagraphss(strs):
    hel = jitang_opendb()
    cur = hel[1].cursor()
    cur.execute("select * from jitang_info where jitang like '%%%s%%'"%strs)
    res = cur.fetchall()
    return res
    cur.close()


def dutang_opendb():
    conn = sqlite3.connect("jitang_date.db")
    cur = conn.execute(
        """create table if not exists dutang_info(id integer PRIMARY KEY autoincrement,dutang varchar(256))""")
    return cur, conn


#  往数据库中添加内容
def dutang_insertData(dutang):
    hel = dutang_opendb()
    hel[1].execute("insert into dutang_info(dutang) values ('%s')" % (dutang))
    hel[1].commit()
    hel[1].close()


# 根据诗名查找诗整体信息
def dutang_select():
    hel = dutang_opendb()
    cur = hel[1].cursor()
    cur.execute("SELECT * FROM dutang_info ORDER BY RANDOM() limit 1")
    res = cur.fetchall()
    return res
    cur.close()
    
#根据诗句模糊查找诗整体信息
def dutang_selectParagraphss(strs):
    hel = dutang_opendb()
    cur = hel[1].cursor()
    cur.execute("select * from dutang_info where dutang like '%%%s%%'"%strs)
    res = cur.fetchall()
    return res
    cur.close()